package com.hitrust.gs.action.productTiptopStock;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.struts.upload.FormFile;

import system.LoginContext;

import com.hitrust.gs.business.GSProductTiptopStockProcesser;
import com.hitrust.gs.business.GSProviderProcessor;
import com.hitrust.gs.form.GSImportExcelForm;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.service.GSBaseAction;
import com.hitrust.gs.service.GSBaseForm;
import com.hitrust.gs.util.RegulaUtil;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSProductUpdatePriceView;
import com.hitrust.gs.vo.ProductIdVO;
import com.hitrust.gs.vo.ProductUpdatePicVO;

public class ProductNotPurchaseInExeclAction extends GSBaseAction {


	public static final String SHEET_NAME = "批量新增或更新";
	public static final String SEP = "%";
	public static String[] HEAD_NAMES = { "序号", "集中编码", "启用日期(格式例如：2013-05-01)","是否可出库（1：可以、0：不可以）" ,"启用时间(0-23)","启用时间(0-59)"};

	public boolean[] COL_CHECK = new boolean[6];

	public GSProductUpdatePriceView manuview = null;
	GSProviderProcessor ppro = new GSProviderProcessor();

	// 匹配不包括 ',",<,>,&的特殊字符
	public static final String regula = "[^'\"<>&]*";
	public static final String zhibao = "^\\d+$";


	public String execute(GSBaseForm aForm) throws Exception {
		GSImportExcelForm tGSImportExcelForm = (GSImportExcelForm) aForm;
		FormFile formFile = tGSImportExcelForm.getExcelfile();

		if (formFile != null) {
			loadDataFromExcel(formFile.getInputStream(),aForm);
		
			
			//去查数据库
		}
		return GSBaseConstants.FORWARD_SUCCESS;
	}

	/**
	 * 从Excel中载入格式化数据，数据项之间以分隔符%隔开
	 * 
	 * @param aInputStream
	 * @param aPage
	 * @param aPageSize
	 * @return
	 * @throws Exception
	 */
	public void loadDataFromExcel(InputStream aInputStream,GSBaseForm aForm)
			throws Exception {
		Workbook tExcelWorkbook = null;

		try {
			tExcelWorkbook = Workbook.getWorkbook(aInputStream);

			Sheet tExcelSheet = tExcelWorkbook.getSheet(SHEET_NAME);
			if (tExcelSheet == null) {
				aForm.getRequest().setAttribute("gohistory","true");	
				throw new GSException("批量导入信息失败:文件格式不正确,无法找到：" + SHEET_NAME);
			}
			for (int i = 1; i < tExcelSheet.getRows(); i++) {
				Cell tCells[] = tExcelSheet.getRow(i);
				String tRowText = "";
				int num = tCells.length;
				for (short j = 0; j < num; j++) {
					Cell tExcelCell = tCells[j];
					if (tExcelCell == null) {
						break;
					}
					String tCellValue = tExcelCell.getContents();
					if (tCellValue.length() == 0) {
						tCellValue = " ";
					}
					tRowText += tCellValue + SEP;
				}

				for (int a = num; a < HEAD_NAMES.length; a++) {
					tRowText += " " + SEP;
				}

				// 检查数据是否正确,如果和数据库中不符则将结果添加到末尾,从第二行开始
				
				tRowText = this.checkExecl(tRowText,i, aForm);
				for (int tJ = 0; tJ < COL_CHECK.length; tJ++) {
					if (COL_CHECK[tJ] == false) {
						aForm.getRequest().setAttribute("gohistory","true");	
						throw new GSException(tRowText);
					}
				}
				GSLogWriter.info(tRowText);
			
			}
		} catch (BiffException e) {
			//GSLogWriter.error(e.getMessage());
			e.printStackTrace();
		} catch (IOException e) {
			//GSLogWriter.error(e.getMessage());
			e.printStackTrace();
		}
		finally {
			if (tExcelWorkbook != null)
				tExcelWorkbook.close();
		}
		
	}

	
	
	public String checkExecl(String aRowText,int row,GSBaseForm aForm) throws Exception {
	
		for (int i = 0; i < COL_CHECK.length; i++) {
			COL_CHECK[i] = true;
		}

		String[] tRecorderColumns = aRowText.split(SEP);

		//判断excel字段不能有特殊字符
		for (int i = 0; i < tRecorderColumns.length; i++) {
			if (!RegulaUtil.matchByString(tRecorderColumns[i], regula)) {
				COL_CHECK[i] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[i] + "不能为',\",<,>,&等特殊字符!";
			}
		}
		//判断excel字段不能为空
		if (tRecorderColumns[0].trim().equals("")) {
			COL_CHECK[0] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[0]
					+ "不能为空！";
		}
		
		if (tRecorderColumns[1].trim().equals("")) {
			COL_CHECK[1] = false;
			return "第"+row+"条记录，" + HEAD_NAMES[1]
					+ "不能为空！";
		}
		
		if (tRecorderColumns[2].trim().equals("")) {
			COL_CHECK[2] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[2]
					+ "不能为空！";
		}

		if (tRecorderColumns[3].trim().equals("")) {
			COL_CHECK[3] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[3]
					+ "不能为空！";
		}
		
		if (tRecorderColumns[4].trim().equals("")) {
			COL_CHECK[4] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[4]
					+ "不能为空！";
		}
		
		if (tRecorderColumns[5].trim().equals("")) {
			COL_CHECK[4] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[4]
					+ "不能为空！";
		}
		
		GSProductTiptopStockProcesser ordProcesser = new GSProductTiptopStockProcesser();
		
		
		
		//验证产品编码，产品编码必须是t_product表里concentricity_code存在的而且is_buy=0是不可采购的，
		//验证不通过提示：“第XX条记录产品部存在或者可采购”
		int isExistProduct = ordProcesser.isExistRow(" select * from t_product  where is_buy = 0 and  concentricity_code =  '"+tRecorderColumns[1]+"'");
		if (isExistProduct == 0) {
			COL_CHECK[1] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[1]
					+ "记录产品不存在或者可采购！";
		}
		
		
		//验证启用时间数据格式
		Pattern pattern = Pattern.compile("^[0-9]*\\.?[0-9]{0,4}");
		Matcher isNum4 = pattern.matcher(tRecorderColumns[4].trim());
		if (!isNum4.matches()) {
			COL_CHECK[4] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[4] + "必须为数值!";
			
		} else if (Double.parseDouble(tRecorderColumns[4].trim()) < 0) {
			COL_CHECK[4] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[4] + "不能小于等于0!";
			
		} else if (Double.parseDouble(tRecorderColumns[4].trim()) > 23) {
			COL_CHECK[4] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[4] + "不能大于23!";
		}
		
		Matcher isNum5 = pattern.matcher(tRecorderColumns[5].trim());
		if (!isNum5.matches()) {
			COL_CHECK[5] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[5] + "必须为数值!";
			
		} else if (Double.parseDouble(tRecorderColumns[5].trim()) < 0) {
			COL_CHECK[5] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[5] + "不能小于等于0!";
			
		}else if (Double.parseDouble(tRecorderColumns[5].trim()) > 59) {
			COL_CHECK[5] = false;
			return "第"+row+"条记录，"  + HEAD_NAMES[5] + "不能大于59!";
		}

		
		//验证启用日期
		 String eL= "^((\\d{2}(([02468][048])|([13579][26]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))";   
	        Pattern p = Pattern.compile(eL);    
	        Matcher m = p.matcher(tRecorderColumns[2].trim());    
	        boolean flag = m.matches();   
	        if(!flag )   
	        {         
	        	COL_CHECK[2] = false;
				return "第"+row+"条记录，"  + HEAD_NAMES[2] + "启用日期格式不正确!";
	        }   
	       
	       SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	       Date currentDate = dateFormat.parse(DateUtil.getCurrentDate("yyyy-MM-dd"));
	    
	       Date excelDate = dateFormat.parse(tRecorderColumns[2]);
	       if (!excelDate.after(currentDate) && !excelDate.equals(currentDate)) {
	    	   	COL_CHECK[2] = false;
				return "第"+row+"条记录，"  + HEAD_NAMES[2] + "启用日期已过期!";
	       }
		
		
		//是否可出库必须是1或者0
		if(!"1".equals(tRecorderColumns[3].trim()) && !"0".equals(tRecorderColumns[3].trim())){
			COL_CHECK[3] = false;
			return "第"+row+"条记录，"  + "状态必须是1或者0!";
		}
		

		int isExist = ordProcesser.isExistRow("   select * from T_PRODUCT_UPDATE_PIC where CONCENTRICITY_CODE = '"+tRecorderColumns[1]+"'  and  ENABLED_TIME like '%"+tRecorderColumns[2]+"%'" );
	
		//登录人的信息
		LoginContext loginContext = (LoginContext)aForm.getSession().getAttribute(GSBaseConstants.LOGIN);
		
		
			//新增记录
			ProductUpdatePicVO vo = new ProductUpdatePicVO();
			ArrayList proIdList = ordProcesser.selectProductId(tRecorderColumns[1]);
			String productId = "";
			if (proIdList.size() > 0) {
				ProductIdVO idVo = (ProductIdVO)proIdList.get(0);
				productId = idVo.getId();
			}
			vo.setProduct_id(productId);
			vo.setConcentricity_code(tRecorderColumns[1]);
			vo.setEnabled_time(tRecorderColumns[2]+" "+tRecorderColumns[4]+":"+tRecorderColumns[5]);
			vo.setPic(tRecorderColumns[3]);
			vo.setCreate_user(loginContext.getUser().getName());
			vo.setCreate_user_id(loginContext.getUser().getId());
			String currentDateStr = DateUtil.getCurrentDate("yyyy-MM-dd HH:mm:ss");
			vo.setCreate_time(currentDateStr);
			vo.setStatus("0");//默认未执行
			vo.setAuditing_status("0");//默认0，待审批
			
			if (isExist == 0) {
				ordProcesser.insertProductUpdatePic(vo);
			}else{
				//ordProcesser.updateProductUpdatePic(vo);
				
				COL_CHECK[1] = false;
				return "第"+row+"条记录，"  + HEAD_NAMES[1]+"'"+tRecorderColumns[1]+"'"
						+ "在同一天中已经插入！";
			}
			
		
		aForm.getRequest().setAttribute("message","批量导入信息成功！");
		return "ok";
	}
	

}
